﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Data;
using System.Text;
using System.ComponentModel.DataAnnotations;

namespace MarkControle.Models
{
    public class Login
    {

        public Funcionario mec;
        private string username; 
        private string password;

        [Required]
        public string Username
        {
            get { return username; }
            set { username = value; }
        }
               
        [Required]
        public string Password
        {
            get { return password; }
            set { password = value; }
        }

        public bool valido()
        {
            mec=Procura(username);
            if (mec.Username == username && mec.Senha == password )
                return true;
            else
                return false;
        }
        
        public bool Responsavel(string user)
        {
            mec = Procura(user);
            if (mec.Username == null)
                return false;
            else
                return true;
        }
        
        public bool bloqueado()
        {
            mec = Procura(username);
            if (mec.Bloq == 0)
                return false;
            else
                return true; 
        }
        
                
        public Funcionario Procura(string nome)
        {
            Funcionario funci=new Funcionario();
            string urlConn = "Database=markcontrole;Data Source=mysql01.markcontrole.hospedagemdesites.ws;User Id=markcontrole;Password=ivan120869; pooling=false";

            // Create objeto Connection
            MySqlConnection conn = new MySqlConnection(urlConn);

            // Open Connection
            try
            {
                conn.Open();
            }
            catch (MySqlException e)
            {
                Console.WriteLine("Error connection by: {0}", e.Message);
            } // end try

            // Validate connection
            if (conn.State.Equals(ConnectionState.Open))
            {

                try
                {
                    // Open transaction



                    // Create query e Command
                    StringBuilder sql = new StringBuilder();
                    sql.Append("SELECT * FROM funcionario ");
                    sql.Append("WHERE USERNAME=@nome");
                    MySqlCommand command = new MySqlCommand(sql.ToString(), conn);

                    // Add Parameters
                    command.Parameters.AddWithValue("@nome", nome);
                    


                    // Insert customer
                    MySqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {

                        
                        
                        funci = new Funcionario((string)reader["NOME"], (string)reader["USERNAME"], (string)reader["SENHA"], (string)reader["CPF"],(int)reader["BLOQ"],(int)reader["ACESSO"],(string)reader["AREA"]);
                       
                    }
                    // Commit transaction

                }
                catch (MySqlException e)
                {


                }
                finally
                {
                    // Close connection
                    conn.Close();
                } // end try
            }

            return funci;
        }
        
        public void Bloqueia(string nome)
        {



            string urlConn = "Database=markcontrole;Data Source=mysql01.markcontrole.hospedagemdesites.ws;User Id=markcontrole;Password=ivan120869; pooling=false";
            MySqlConnection conn = new MySqlConnection(urlConn);

            try
            {
                conn.Open();
            }
            catch (MySqlException e)
            {
                Console.WriteLine("Error connection by: {0}", e.Message);
            } // end try



            if (conn.State.Equals(ConnectionState.Open))
            {
                MySqlTransaction transaction = null;
                try
                {
                    // Open transaction
                    transaction = conn.BeginTransaction();

                    String sql = "UPDATE funcionario SET BLOQ=@bloq WHERE USERNAME=@nome";

                    MySqlCommand command = new MySqlCommand(sql, conn);

                    command.Parameters.AddWithValue("@nome", nome);
                    command.Parameters.AddWithValue("@bloq", 1);
               
                   
                    command.ExecuteNonQuery();



                    transaction.Commit();
                }
                catch (MySqlException e)
                {
                    transaction.Rollback();
                }
                finally
                {
                    conn.Close();
                }




            }

        











        }
        
        
        public void Desloqueia(string nome)
        {



            string urlConn = "Database=markcontrole;Data Source=mysql01.markcontrole.hospedagemdesites.ws;User Id=markcontrole;Password=ivan120869; pooling=false";
            MySqlConnection conn = new MySqlConnection(urlConn);

            try
            {
                conn.Open();
            }
            catch (MySqlException e)
            {
                Console.WriteLine("Error connection by: {0}", e.Message);
            } // end try



            if (conn.State.Equals(ConnectionState.Open))
            {
                MySqlTransaction transaction = null;
                try
                {
                    // Open transaction
                    transaction = conn.BeginTransaction();

                    String sql = "UPDATE funcionario SET BLOQ=@bloq WHERE USERNAME=@nome";

                    MySqlCommand command = new MySqlCommand(sql, conn);

                    command.Parameters.AddWithValue("@nome", nome);
                    command.Parameters.AddWithValue("@bloq", 0);


                    command.ExecuteNonQuery();



                    transaction.Commit();
                }
                catch (MySqlException e)
                {
                    transaction.Rollback();
                }
                finally
                {
                    conn.Close();
                }




            }













        }

    }
}
